
import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="111111",
    database="py",
    chartset="utf8",
    port=3306
)

mycursor = mydb.cursor()
#
mycursor.execute("Create table person(usename varchar(255),"
                 "age int,sex varchar(255),high int)")

insert_sql = ("INSERT INTO person(usename,age,sex,high)VALUES(%s,%s,%s,%s)")

print("=====================================================")
# 添加10个用户
for i in range(10):
    username = input("添加人的的姓名：")
    age = int(input("添加人的年龄："))
    sex = input("添加人的性别：")
    high = int(input("添加人的身高："))
    print("添加成功\n")
    values = (username, age, sex, high)
    mycursor.execute(insert_sql, values)
    mydb.commit()
print("=====================================================")
# 批量添加用户
values = [('张三', 27, 'male', 180),
          ('李四', 25, 'male', 170),
          ('aaa',66,'male',170),
          ('as',22,'female',160)]

mycursor.executemany(insert_sql, values)
mydb.commit()
print("=====================================================")

mydb.close()
mycursor.close()

print("----------------------------------------------------------------------")
#从execl表中读取数据，用pymysql插入到mysql数据库中
import pymysql
from openpyxl import load_workbook
#连接本地数据库
mydb = pymysql.connect(
    host="localhost",
    user="root",
    password="111111",
    database="py",
    charset="utf8",
    port=3306
)

mycursor = mydb.cursor()
#创建userinfo表
mycursor.execute("CREATE TABLE userinfo "
                 "("
                 "id int(11),"
                 "idcard varchar(50),"
                 "username varchar(50),"
                 "realname varchar(50),"
                 "pwd varchar(50),"
                 "telphone varchar(12),"
                 "email varchar(100),"
                 "age int(11),"
                 "sex varchar(20),"
                 "address varchar(200),"
                 "hiredate date,"
                 "sal double(9,2),"
                 "job varchar(100),"
                 "company varchar(100)"
                 ")")

#插入数据
sql = "INSERT INTO userinfo (id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#读取excel文件里的数据
workbook = load_workbook(filename='../data/baidu-员工的人员信息 .xlsx')
sheet1 = workbook.active
list1 = []
# for i in sheet1.iter_rows(min_row=2,max_row=2,min_col=1,max_col=14):
#     for j in i:
#         list1.append(j.value)

# 先行后列
for i in sheet1.iter.rows(min_row=2,max_row=65535,min_col=1,max_col=14):
    for j in i:
        list1.append(j.value)
mycursor.executemany(sql, list1)
mydb.commit()

#使用execute插入数据
for row in sheet1.iter_rows(min_row=2,values_only=True):
    mycursor.execute(sql, row)


#使用executemany批量插入数据
for row in sheet1.iter_rows(min_row=2,values_only=True):
    list1.append(row)
mycursor.executemany(sql, list1)

mydb.commit()
#关闭游标
mycursor.close()
# #关闭数据库连接
mydb.close()





